from jira import JIRA
import smtplib
from email.mime.text import MIMEText
from datetime import datetime
import pandas as pd
from io import StringIO
import xlsxwriter
import os
from tabulate import tabulate  # 确保正确导入 tabulate


# 初始化Jira客户端
jira_url = 'http://*******com/'
username = '******'
password = '*********'

# 连接到Jira服务器
jira = JIRA(server=jira_url, basic_auth=(username, password))

# 指定过滤器的ID
filter_id = '27200'  # 示例ID，请替换为实际的过滤器ID

# 获取过滤器的详情
filter = jira.filter(filter_id)

# 获取过滤器的查询结果（issues）
issues = jira.search_issues(filter.jql, maxResults=1000)  # 可调整maxResults来获取更多问题

# 定义时间格式
def convert_time_format(time_str):
    """
    将Jira的时间格式转换为 'x月x日' 格式。
    """
    # 假设时间字符串格式是 '%Y-%m-%dT%H:%M:%S.%f%z'
    dt = datetime.strptime(time_str, '%Y-%m-%dT%H:%M:%S.%f%z')
    return dt.strftime('%m月%d日')

# 转换工单耗时函数，单位为秒，转换为小时并保留2位小数
def convert_timespent_to_hours(timespent):
    try:
        # 将秒数转换为小时，并保留两位小数
        if timespent != 'N/A':
            hours = timespent / 3600
            return f"{hours:.2f}"
        else:
            return 'N/A'
    except Exception as e:
        return 'N/A'

# 统计总工单数和每种工单等级的数量
total_issues = len(issues)
priority_counts = {}
for issue in issues:
    priority = issue.fields.priority.name if getattr(issue.fields, 'priority', None) else 'N/A'
    priority_counts[priority] = priority_counts.get(priority, 0) + 1

# 列标题和对应的宽度及对齐方式
columns = {
    '工单主题': {'width': 'auto', 'line-height': '38px','alignment': 'left'  },
    '工单单号': {'width': '88px', 'line-height': '38px','alignment': 'center'},
    '工单等级': {'width': '88px', 'line-height': '38px','alignment': 'center'},
    '工单耗时': {'width': '88px', 'line-height': '38px','alignment': 'center'},
    '任务类型': {'width': '88px', 'line-height': '38px', 'alignment': 'center'},
    '创建时间': {'width': '136px','line-height': '38px','alignment': 'center'},
    '完成时间': {'width': '136px','line-height': '38px','alignment': 'center'},
    '工单状态': {'width': '88px', 'line-height': '38px', 'alignment': 'center'},
    '运维人员': {'width': '88px', 'line-height': '38px', 'alignment': 'center'}
}


def convert_time_format(time_str):
    """
    将 '2025-02-28T20:44:38.000+0800' 格式的字符串转换为 'x月x日'.
    """
    # 解析原始时间字符串
    dt = datetime.strptime(time_str, '%Y-%m-%dT%H:%M:%S.%f%z')
    # 转换为目标格式
    return dt.strftime('%m月%d日')


# 转换为 HTML 表格
def convert_to_html_table(issues):

    data = []

    # 开始表格的 HTML
    html_table = "<table border='1' style='border-collapse: collapse; width: 1366px;'>\n"
    # 添加运维日报标题行
    html_table += "<thead>\n"
    html_table += "<tr><th colspan='11' style='text-align: center; font-size: 20px; font-weight: bold; padding: 10px; line-height: 38px; background-color: #f2f2f2;'>运维周报</th></tr>\n"
    html_table += "<tr>"
    # 根据 columns 字典动态生成表头
    html_table += "<tr>"
    html_table += "<th style='text-align: left; width : auto'>项目名称</th>"  # 为组件添加一列
    for column_name, settings in columns.items():
        width = settings['width']
        alignment = settings['alignment']
        html_table += f"<th style='width: {width}; text-align: {alignment}; height: 28px;'>{column_name}</th>"
    html_table += "</tr>\n"
    html_table += "</thead>\n"
    html_table += "<tbody>\n"

    for issue in issues:
        # 获取每个工单的相关字段
        summary = issue.fields.summary  # 工单主题
        key = issue.key                 # 工单单号
        priority = issue.fields.priority.name if issue.fields.priority else 'N/A'     # 工单等级
        timespent = convert_timespent_to_hours(issue.fields.timespent)  # 将工单耗时转换为小时
        customfield_13001 = issue.fields.customfield_13001 if issue.fields.priority else 'N/A' # 任务类型
        created = convert_time_format(issue.fields.created)  # 转换 Created 时间
        updated = convert_time_format(issue.fields.updated)  # 转换 Updated 时间
        status = issue.fields.status.name if issue.fields.status else 'N/A'           # 工单状态
        assignee = issue.fields.assignee.displayName if issue.fields.assignee else 'Unassigned'  # 负责人
        # 获取组件信息
        components = issue.fields.components
        components_list = ", ".join([component.name for component in components]) if components else 'N/A'

        # 将数据添加到data列表
        data.append({
            '项目名称': components_list,
            '工单主题': summary,
            '工单单号': key,
            '工单等级': priority,
            '工单耗时': timespent,
            '任务类型': customfield_13001,
            '创建时间': created,
            '完成时间': updated,
            '工单状态': status,
            '运维人员': assignee
        })

        # 生成每一行的数据，按照列顺序排列
        html_table += "<tr  style='height: 28px;'>"
        html_table += f"<td style='text-align: left; '>{components_list}</td>"   # 项目名称
        html_table += f"<td style='text-align: {columns['工单主题']['alignment']};'>{summary}</td>"
        html_table += f"<td style='text-align: {columns['工单单号']['alignment']};'>{key}</td>"
        html_table += f"<td style='text-align: {columns['工单等级']['alignment']};'>{priority}</td>"
        html_table += f"<td style='text-align: {columns['工单耗时']['alignment']};'>{timespent}</td>"
        html_table += f"<td style='text-align: {columns['任务类型']['alignment']};'>{customfield_13001}</td>"
        html_table += f"<td style='text-align: {columns['创建时间']['alignment']};'>{created}</td>"
        html_table += f"<td style='text-align: {columns['完成时间']['alignment']};'>{updated}</td>"
        html_table += f"<td style='text-align: {columns['工单状态']['alignment']};'>{status}</td>"
        html_table += f"<td style='text-align: {columns['运维人员']['alignment']};'>{assignee}</td>"
        html_table += "</tr>\n"
    html_table += "</tbody>\n"
    html_table += "</table>\n"

    # 使用data列表创建DataFrame
    df = pd.DataFrame(data)
    # 导出Excel文件
    output_dir = r'D:\check'
    os.makedirs(output_dir, exist_ok=True)
    excel_path = os.path.join(output_dir, 'issues.xlsx')
    df.to_excel(excel_path, index=False)
    print(f"Excel文件已成功导出到: {excel_path}")

    return html_table

# 将JIRA问题数据转换为HTML表格
html_output = convert_to_html_table(issues)

# 保存HTML到文件
html_path = os.path.join(r'D:\check', 'issues.html')
with open(html_path, 'w', encoding='utf-8') as f:
    f.write(html_output)
print(f"HTML文件已成功导出到: {html_path}")
